﻿using System.Data;
using System.Data.SqlClient;
using MasterDatabase;


namespace LayoutControl
{
    public class LinesColletion_DataBase : SQL_APPL
    {
        //public static string MasterDatabase_Connection_Str = @"server=(local)\SQLEXPRESS;database=JOB_ASSIGNMENT_DB;Integrated Security = TRUE;Data Source=PHAMQUANGTHAI\DHO_SQLEXPRESS";
        public static string MasterDatabase_Connection_Str = LayoutControlSetting.Connection_Str;

        public DataTable List_Line_dtb = new DataTable();
        public DataSet List_Line_ds = new DataSet();
        public SqlDataAdapter List_Line_da;

        public DataTable List_WST_dtb = new DataTable();
        public DataSet List_WST_ds = new DataSet();
        public SqlDataAdapter List_WST_da;


        public DataTable Load_List_of_Line()
        {
            string sql_cmd = @"SELECT distinct [Line_ID],[Line_Name] FROM " + LayoutControlSetting.DatabaseName;

            if (List_Line_dtb != null)
            {
                List_Line_dtb.Clear();
            }
            List_Line_dtb = Get_SQL_Data(MasterDatabase_Connection_Str, sql_cmd, ref List_Line_da, ref List_Line_ds);

            return List_Line_dtb;
        }

        public string FindLineID(string lineName)
        {
            string sql_cmd = @"SELECT distinct [Line_ID]
                                FROM " + LayoutControlSetting.DatabaseName;

            sql_cmd += " WHERE [Line_Name] = '" + lineName + "'";

            if (List_Line_dtb != null)
            {
                List_Line_dtb.Clear();
            }
            List_Line_dtb = Get_SQL_Data(MasterDatabase_Connection_Str, sql_cmd, ref List_Line_da, ref List_Line_ds);

            if (List_Line_dtb.Rows.Count > 0)
            {
                return List_Line_dtb.Rows[0][0].ToString();
            }

            return "";
        }

        public string FindLineName(string lineID)
        {
            string sql_cmd = @"SELECT distinct [Line_Name]
                                FROM " + LayoutControlSetting.DatabaseName;

            sql_cmd += " WHERE [Line_ID] = '" + lineID + "'";

            if (List_Line_dtb != null)
            {
                List_Line_dtb.Clear();
            }
            List_Line_dtb = Get_SQL_Data(MasterDatabase_Connection_Str, sql_cmd, ref List_Line_da, ref List_Line_ds);

            if (List_Line_dtb.Rows.Count > 0)
            {
                return List_Line_dtb.Rows[0][0].ToString();
            }

            return "";
        }

        public DataTable Load_List_of_WST(string line_id)
        {
            string sql_cmd = @"SELECT distinct [WST_ID],[WST_Name],[WST_x],[WST_y],[WST_width],[WST_heigh] 
                                FROM " + LayoutControlSetting.DatabaseName;
            sql_cmd += " WHERE [Line_ID] = '" + line_id + "'";

            if (List_WST_dtb != null)
            {
                List_WST_dtb.Clear();
            }
            List_WST_dtb = Get_SQL_Data(MasterDatabase_Connection_Str, sql_cmd, ref List_WST_da, ref List_WST_ds);
            return List_WST_dtb;
        }

        public DataTable Load_All_LineInfo(string line_id)
        {
            string sql_cmd = @"SELECT * FROM " + LayoutControlSetting.DatabaseName;
            sql_cmd += " WHERE [Line_ID] = '" + line_id + "'";

            if (List_WST_dtb != null)
            {
                List_WST_dtb.Clear();
            }
            List_WST_dtb = Get_SQL_Data(MasterDatabase_Connection_Str, sql_cmd, ref List_WST_da, ref List_WST_ds);
            return List_WST_dtb;
        }

        public void Save_LineInfo(Line line)
        {
            string lineID = FindLineID(line.LineName);

            DataTable tbl = Load_FullLineInfo(lineID);

            //Update datatable with new info
            foreach (DataRow row in tbl.Rows)
            {
                string WST_ID = row["WST_ID"].ToString();

                int x, y, w, h;

                //Get the information of object and update to the table
                if (line.Get_WST_LocationAndSize(WST_ID,out x, out y, out w, out h))
                {
                    row["WST_x"] = x;
                    row["WST_y"] = y;
                    row["WST_width"] = w;
                    row["WST_heigh"] = h;
                }
            }

            Update_SQL_Data(List_Line_da, tbl);
            return;
            
        }

        public DataTable Load_FullLineInfo(string lineID)
        {
            string sql_cmd = @"SELECT * FROM " + LayoutControlSetting.DatabaseName;
            sql_cmd += " WHERE Line_ID = '" + lineID + "'";

            if (List_Line_dtb != null)
            {
                List_Line_dtb.Clear();
            }
            List_Line_dtb = Get_SQL_Data(MasterDatabase_Connection_Str, sql_cmd, ref List_Line_da, ref List_Line_ds);

            return List_Line_dtb;
        }


    }
}
